package org.common.utils.exceltosql;
import com.alibaba.excel.EasyExcel;
import org.apache.commons.io.FileUtils;
import org.common.utils.tool.CollectionTool;
import org.common.utils.tool.StringTool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.util.*;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * excel转sql工具， 示例xlsx文件在resources/excel目录中，
 * 格式如下：
 * 1行：字段名称[name]
 * 2行：字段类型[int]
 * 3行：字段描述[这是个名称]
 * 4行：字段范围[A/S/C], A全部/S服务器/C客户端;
 * @author zhouzhibing
 * @date 2024/11/27
 */
public class ExcelToSqlFile {

    private static final Logger log = LoggerFactory.getLogger(ExcelToSqlFile.class);

    /**
     * 开始数据列索引
     */
    private static final int START_DATA_COLUMN_INDEX = 0;

    /**
     * 开始数据行索引
     */
    private static final int START_DATA_ROW_INDEX = 4;

    /**
     * 类型行索引（字段类型行）
     */
    private static final int TYPE_ROW_INDEX = 1;
    /**
     * 描述行索引（字段说明行）
     */
    private static final int DESC_ROW_INDEX = 2;
    /**
     * 范围行索引（字段范围行，A/S/C）
     */
    private static final int RANGE_ROW_INDEX = 3;


    public static void generateSql(String xlsxDir , String sqlDir) {
        Collection<File> files = getXlsxFiles(xlsxDir);
        AtomicInteger successCount = new AtomicInteger();

        try (ForkJoinPool forkJoinPool = ForkJoinPool.commonPool()) {
            for (File file : files) {
                forkJoinPool.execute(() -> {
                    generateSqlFile(file , sqlDir);
                    successCount.incrementAndGet();
                });
            }
            forkJoinPool.awaitQuiescence(Long.MAX_VALUE, TimeUnit.SECONDS);
            log.info("ExcelToSqlFile.generateSql successCount = {}", successCount.get());
        }
    }

    public static void generateSql(String xlsxDir , String sqlDir , String fileNames) {
        File file = FileUtils.getFile(xlsxDir, fileNames);
        generateSqlFile(file , sqlDir);
    }


    /**
     * 获取该目录下所有 excel文件列表
     * @param xlsxDir excel目录
     * @return excel文件列表
     */
    private static Collection<File> getXlsxFiles(String xlsxDir) {
        ExcelFileFilter excelFileFilter = new ExcelFileFilter();
        return FileUtils.listFiles(new File(xlsxDir) , excelFileFilter, excelFileFilter);
    }

    /**
     * 根据指定excel文件和保存目录，生成sql文件
     * @param file excel文件
     * @param sqlDir 保存目录
     */
    private static void generateSqlFile(File file , String sqlDir) {
        String fileName = file.getName();
        String simpleName = fileName.substring(0, fileName.indexOf("."));
        String tableName = StringTool.toSnakeString(simpleName);

        ExcelReadListener readExcelListener = new ExcelReadListener((headMap, rowMap) -> {
            ExcelToSqlFile.doParser(tableName ,headMap , rowMap , sqlDir);
        });

        EasyExcel.read(file, readExcelListener).sheet(0).doRead();
    }

    /**
     * 执行解析
     * @param tableName 表名
     * @param headMap 表头数据
     * @param rowMap 行数据
     */
    private static void doParser(String tableName , Map<Integer, String> headMap , Map<Integer, Map<Integer, String>> rowMap , String sqlDir) {
        if (CollectionTool.isEmpty(headMap)) {
            throw new RuntimeException("ExcelToSqlFile.doParser headMap is empty , tableName = " + tableName);
        }
        if (CollectionTool.isEmpty(rowMap)) {
            throw new RuntimeException("ExcelToSqlFile.doParser rowMap is empty , tableName = " + tableName);
        }

        List<Integer> columnIndexList = getColumnIndexList(tableName , headMap, rowMap);
        String table = generateTable(tableName, columnIndexList, headMap, rowMap);
        String insert = generateInsert(tableName, columnIndexList, headMap, rowMap);

        String body = table + "\n" + insert;
        writeFile(tableName , body , sqlDir);
    }

    /**
     * 生成insert插入语句
     * @param tableName 表名
     * @param headMap 表头数据
     * @param rowMap 行数据
     * @return 返回生成的插入语句
     */
    private static String generateInsert(String tableName, List<Integer> columnIndexList , Map<Integer, String> headMap, Map<Integer, Map<Integer, String>> rowMap) {
        if(headMap == null || headMap.isEmpty())
            return "";

        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        int columnSize = columnIndexList.size();
        int rowSize = rowMap.size();

        for (int index = 0; index < columnSize; index++) {
            Integer columnIndex = columnIndexList.get(index);
            String headValue = headMap.get(columnIndex);
            StringTool.byIndexString(columns, "`" ,headValue, "`","," , index , columnSize - 1);
        }

        for (int rowIndex = START_DATA_ROW_INDEX; rowIndex <= rowSize; rowIndex++) {
            values.append("\n(");
            Map<Integer, String> currentRowMap = rowMap.get(rowIndex);

            for (int index = 0; index < columnSize; index++) {
                Integer columnIndex = columnIndexList.get(index);
                String columnValue = currentRowMap.get(columnIndex);
                if(columnValue == null)
                    StringTool.byIndexString(values ,null, "," , index , columnSize - 1);
                else {
                    if(columnValue.contains("\"")) {
                        columnValue = columnValue.replace("\"", "\\\"");
                    }

                    if(columnValue.equalsIgnoreCase("true") || columnValue.equalsIgnoreCase("false")) {
                        StringTool.byIndexString(values ,columnValue,"," , index , columnSize - 1);
                    } else {
                        StringTool.byIndexString(values, "\"" ,columnValue, "\"","," , index , columnSize - 1);
                    }
                }
            }

            StringTool.byIndexString(values,")", "," , rowIndex , rowSize);
        }
        return "INSERT INTO `" + tableName + "`(" + columns.toString() + ") VALUES" + values.toString();
    }

    /**
     * 生成表结构
     * @param tableName 表名
     * @param columnIndexList 列索引列表
     * @param headMap 表头数据
     * @param rowMap 行数据
     * @return 表结构语句
     */
    private static String generateTable(String tableName, List<Integer> columnIndexList , Map<Integer, String> headMap, Map<Integer, Map<Integer, String>> rowMap) {
        if(headMap == null || headMap.isEmpty())
            return "";

        StringBuilder content = new StringBuilder();
        content.append("DROP TABLE IF EXISTS `").append(tableName).append("`;\n\n");
        content.append("CREATE TABLE `").append(tableName).append("`(\n");
        Map<Integer, String> typeRowMap = rowMap.get(TYPE_ROW_INDEX);
        Map<Integer, String> descRowMap = rowMap.get(DESC_ROW_INDEX);
        int columnSize = columnIndexList.size();

        for (int index = 0; index < columnSize; index++) {
            Integer columnIndex = columnIndexList.get(index);
            //没有指定类型
            String typeValue = typeRowMap.get(columnIndex);
            if(typeValue == null)
                continue;

            String headValue = headMap.get(columnIndex);
            String mysqlType = getMysqlType(typeValue);
            String descValue = descRowMap.get(columnIndex);

            StringBuilder rightString = new StringBuilder();
            rightString.append(" ").append(mysqlType).append(" COMMENT \"").append(descValue).append("\"");

            StringTool.byIndexString(content, "`" ,headValue, "`" + rightString,",\n" , index , columnSize - 1);
        }
        content.append("\n)");
        content.append("ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '' ROW_FORMAT = DYNAMIC; \n");

        return content.toString();
    }

    /**
     * 获取使用的列索引列表
     * @param headMap 头数据列表
     * @return 返回列索引列表
     */
    private static List<Integer> getColumnIndexList(String tableName , Map<Integer, String> headMap , Map<Integer, Map<Integer, String>> rowMap) {
        int size = headMap.size();
        Map<Integer, String> rangeRowMap = rowMap.get(RANGE_ROW_INDEX);
        List<Integer> indexList = new ArrayList<>(size);

        for (int i = START_DATA_COLUMN_INDEX; i < size; i++) {
            String columnValue = headMap.get(i);
            if(columnValue == null)
                continue;

            //该字段不在后端使用
            String rangeValue = rangeRowMap.get(i);
            if("C".equals(rangeValue)) {
                continue;
            }

            //字符串不合法
            if (!StringTool.isValidString(columnValue)) {
                log.warn("ExcelToSqlFile.getColumnIndexList columnValue is invalid , tableName = {} , headValue = {} " , tableName , columnValue);
                continue;
            }

            indexList.add(i);
        }
        return indexList;
    }

    /**
     * 根据内容写入指定目录下的文件
     * @param tableName 表名
     * @param content 内容
     * @param sqlDir sql目录
     */
    private static void writeFile(String tableName , String content , String sqlDir) {
        String fileName = sqlDir + tableName + ".sql";
        File file = new File(fileName);
        try {
            FileUtils.write(file , content , Charset.defaultCharset());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        log.info("writeFile : " + file.getAbsolutePath());
    }

    /**
     * 配置表中的字段类型，转成mysql中的类型
     * @param type 配置表中字段类型
     * @return mysql中的类型
     */
    private static String getMysqlType(String type) {
        return switch (type) {
            case "int" -> "int(11)";
            case "long" -> "bigint(20)";
            case "float" -> "float";
            case "double" -> "double";
            case "bool" -> "tinyint(1)";
            case "boolean" -> "tinyint(1)";
            case "string" -> "text";
            case "text" -> "text";
            case "mediumtext" -> "mediumtext";
            case "longtext" -> "longtext";
            default -> "text";
        };
    }

}
